package sql

import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession

object E1_sql_loadData {

  def main(args: Array[String]): Unit = {
    loadData()
  }

  def loadData(): Unit = {
    // 创建 Spark 运行配置对象
    val sparkConf = new SparkConf().setMaster("local[*]").setAppName("loadData")
    //创建 SparkSession 对象
    val spark: SparkSession = SparkSession.builder().config(sparkConf).enableHiveSupport().getOrCreate()

    // 导入隐式转换
    import spark.implicits._
    // 创建3张表并导入初始化数据
    spark.sql("use spark")
    spark.sql("select current_database()").show
    spark.sql("drop table if exists user_visit_action")
    spark.sql(
      """
        |CREATE TABLE if not exists `user_visit_action`(
        |   `date` string,
        |   `user_id` bigint,
        |   `session_id` string,
        |   `page_id` bigint,
        |   `action_time` string,
        |   `search_keyword` string,
        |   `click_category_id` bigint,
        |   `click_product_id` bigint,
        |   `order_category_ids` string,
        |   `order_product_ids` string,
        |   `pay_category_ids` string,
        |   `pay_product_ids` string,
        |   `city_id` bigint)
        |row format delimited fields terminated by ','
        |""".stripMargin
    )
    spark.sql(
      """
        |load data local inpath '/D:/workspace/lab/learnbigdata/learnspark/doc/spark-sql/data/user_visit_action.txt' into table user_visit_action
      """.stripMargin
    )
    spark.sql("drop table if exists product_info")
    spark.sql(
      """
        |create table if not exists  `product_info`(
        | `product_id` bigint,
        | `product_name` string,
        | `extend_info` string)
        | row format delimited
        | fields terminated by '\t'
        |""".stripMargin
    )
    spark.sql(
      """
        |load data local inpath '/D:\\workspace\\lab\\learnbigdata\\learnspark\\doc\\spark-sql\\data\\product_info.txt' into table product_info
        |""".stripMargin
    )
    spark.sql("drop table if exists city_info")
    spark.sql(
      """
        |create table if not exists  `city_info`(
        | `city_id` bigint,
        | `city_name` string,
        | `area` string)
        | row format delimited fields terminated by '\t'
        |""".stripMargin
    )
    spark.sql(
      """
        |load data local inpath '/D:\\workspace\\lab\\learnbigdata\\learnspark\\doc\\spark-sql\\data\\city_info.txt' into table city_info
        |""".stripMargin
    )
    // 查看其中 city_info 表的模式信息，包括列名、数据类型和其他元数据信息
    spark.sql("DESCRIBE user_visit_action").show()

    // 查看 city_info 表的数据
    spark.sql("""select * from city_info""").show

    //关闭 Spark
    spark.stop()
  }

}
